/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import java.sql.*;
import java.util.ArrayList;
import DTO.SanPham_DTO;
/**
 *
 * @author phihai
 */
public class SanPham_DAO {
    
    Database db = new Database();
    Connection conn = null;
    PreparedStatement pst = null;
    
    public ArrayList<SanPham_DTO> DSRD_SanPham() throws Exception
    {
        ArrayList<SanPham_DTO> arr = new ArrayList<SanPham_DTO>(); 
        String sql = "SELECT Top(6)* FROM SanPham order by NEWID()";       
        conn = db.open();
        pst = conn.prepareStatement(sql);
        
        ResultSet rs = pst.executeQuery();
        while (rs.next())
        {
            SanPham_DTO dt = new SanPham_DTO();   
            dt.setIDSanPham(rs.getInt("IDSanPham"));
            dt.setTenSP(rs.getString("TenSP"));          
            dt.setDonGia(rs.getInt("DonGia"));
            dt.setSoLuong(rs.getInt("SoLuong"));           
            dt.setMoTa(rs.getString("MoTa"));
            dt.setUrl(rs.getString("Url"));
            dt.setIDDanhMuc(rs.getInt("IDDanhMuc"));
            arr.add(dt);
        }
        db.close();
        return arr;
    }
    
    public ArrayList<SanPham_DTO> DS_SanPham() throws Exception
    {
        ArrayList<SanPham_DTO> arr = new ArrayList<SanPham_DTO>(); 
        String sql = "SELECT * FROM SanPham";       
        conn = db.open();
        pst = conn.prepareStatement(sql);
        
        ResultSet rs = pst.executeQuery();
        while (rs.next())
        {
            SanPham_DTO dt = new SanPham_DTO();   
            dt.setIDSanPham(rs.getInt("IDSanPham"));
            dt.setTenSP(rs.getString("TenSP"));         
            dt.setDonGia(rs.getInt("DonGia"));
            dt.setSoLuong(rs.getInt("SoLuong"));           
            dt.setMoTa(rs.getString("MoTa"));
            dt.setUrl(rs.getString("Url"));
            dt.setIDDanhMuc(rs.getInt("IDDanhMuc"));
            arr.add(dt);
        }
        db.close();
        return arr;
    }
    
    public SanPham_DTO LaySPTheoID(int ID) throws Exception
    {
        ArrayList<SanPham_DTO> arr = new ArrayList<SanPham_DTO>(); 
        String sql = "SELECT * FROM SanPham WHERE IDSanPham ="+ID ;  
        conn = db.open();
        pst = conn.prepareStatement(sql);
        
        ResultSet rs = pst.executeQuery();
        SanPham_DTO dt = new SanPham_DTO(); 
        while (rs.next())
        {              
            dt.setIDSanPham(rs.getInt("IDSanPham"));
            dt.setTenSP(rs.getString("TenSP"));           
            dt.setDonGia(rs.getInt("DonGia"));
            dt.setSoLuong(rs.getInt("SoLuong"));           
            dt.setMoTa(rs.getString("MoTa"));
            dt.setUrl(rs.getString("Url"));
            dt.setIDDanhMuc(rs.getInt("IDDanhMuc"));
            
        }
        db.close();
        return dt;
    }
    
    public ArrayList<SanPham_DTO>TimSPTheoTen(String tenSP) throws Exception
    {
        ArrayList<SanPham_DTO> arr = new ArrayList<SanPham_DTO>(); 
        String sql = "SELECT * FROM SanPham WHERE TenSP LIKE '" + tenSP +"%'" ;  
        conn = db.open();
        pst = conn.prepareStatement(sql);
        
        ResultSet rs = pst.executeQuery();
        while (rs.next())
        {
            SanPham_DTO dt = new SanPham_DTO();   
            dt.setIDSanPham(rs.getInt("IDSanPham"));
            dt.setTenSP(rs.getString("TenSP"));           
            dt.setDonGia(rs.getInt("DonGia"));
            dt.setSoLuong(rs.getInt("SoLuong"));           
            dt.setMoTa(rs.getString("MoTa"));
            dt.setUrl(rs.getString("Url"));
            dt.setIDDanhMuc(rs.getInt("IDDanhMuc"));
            arr.add(dt);
        }
        db.close();
        return arr;
    }
    
    public void ThemSanPham(SanPham_DTO sp) throws Exception
    {
         String sql = "INSERT INTO SanPham (TenSP,DonGia,SoLuong,MoTa,Url,IDDanhMuc) VALUES (?,?,?,?,?,?)";
         conn = db.open(); 
         pst = conn.prepareStatement(sql);
               
         pst.setString(1, sp.getTenSP());         
         pst.setInt(2, sp.getDonGia());
         pst.setInt(3, sp.getSoLuong());
         pst.setString(4, sp.getMoTa());
         pst.setString(5, sp.getUrl());
         pst.setInt(6, sp.getIDDanhMuc());
        
         pst.executeUpdate();
         
         db.close();
    }
    
     public void CapNhatSanPham(SanPham_DTO sp) throws Exception
    {
         String sql = "UPDATE SanPham SET TenSP=?,DonGia=?,SoLuong=?,MoTa=?,Url=?,IDDanhMuc=? WHERE IDSanPham=?";
         conn = db.open(); 
         pst = conn.prepareStatement(sql);
               
         pst.setString(1, sp.getTenSP());         
         pst.setInt(2, sp.getDonGia());
         pst.setInt(3, sp.getSoLuong());
         pst.setString(4, sp.getMoTa());
         pst.setString(5, sp.getUrl());
         pst.setInt(6, sp.getIDDanhMuc());
         pst.setInt(7, sp.getIDSanPham());
        
         pst.executeUpdate();
         
         db.close();
    }
    
    public int XoaSanPham(int id_sp) throws Exception
    {
         String sql = "DELETE SanPham WHERE IDSanPham=?";
         conn = db.open(); 
         pst = conn.prepareStatement(sql);
         
         pst.setInt(1, id_sp);
         
         int kq = pst.executeUpdate();
         
         db.close();
         return kq;
    }
}
